package com.junzhiit.nengji.shop.dao.impl;

import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.bainaeco.framework.util.DateUtil;
import com.junzhiit.nengji.shop.bo.CountOrdersStatusBO;
import com.junzhiit.nengji.shop.bo.OrdersAndDetailsBO;
import com.junzhiit.nengji.shop.bo.OrdersBO;
import com.junzhiit.nengji.shop.bo.OrdersPrintingBO;
import com.junzhiit.nengji.shop.bo.OrdersTransactionInfoBO;
import com.junzhiit.nengji.shop.dao.OrdersDAO;
import com.junzhiit.nengji.shop.model.Orders;
import com.junzhiit.nengji.shop.model.UserEvaluate;
import com.junzhiit.nengji.shop.model.enums.Is;
import com.junzhiit.nengji.shop.model.enums.IsChainStatus;
import com.junzhiit.nengji.shop.model.enums.OrderGoodsStatus;
import com.junzhiit.nengji.shop.model.enums.OrderStatus;
import com.junzhiit.nengji.shop.model.enums.OrderType;

@Repository
public class OrdersDAOImpl extends AbstractBaseDAO<Orders> implements OrdersDAO {

	@Override
	public int getTotalCount(OrdersBO ordersBO, Boolean isRefund) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();
		sql.append("select count(o.id) from orders o");

		if (isRefund) {// 查看退款商品对应的订单
			sql.append(
					" inner join (select og.order_id from orders_goods og  where (og.`status` = ? or og.`status` = ?)");
			sql.append(" group by og.order_id) og on og.order_id = o.id");
			params.setInt(OrderGoodsStatus.REFUND.toIntValue());
			params.setInt(OrderGoodsStatus.REFUND_SUCCESS.toIntValue());
		}

		sql.append(" inner join shop s on s.id=o.shop_id");
		sql.append(" inner join user u on u.id=o.user_id");
		
		if(StringUtils.isNotBlank(ordersBO.getChainId())){
			sql.append(" inner join chain c on s.chain_id=c.id");
		}
		sql.append(" left join address a on a.id= o.address_id");
		sql.append(" left join logistics l on o.logistics_id = l.id");
		sql.append(" left join member_card_use mcu on o.id=mcu.order_id and mcu.privilege_type=?");
		params.setInt(Is.YES.toIntValue());
		sql.append(" where o.is_delete = ?");
		params.setInt(IsDelete.NO.toIntValue());
		if(StringUtils.isNotBlank(ordersBO.getChainId())){
			sql.append(" and s.chain_id = ?");
			params.setString(ordersBO.getChainId());
		}
		if (StringUtils.isNotBlank(ordersBO.getShopId())) {
			sql.append(" and o.shop_id = ?");
			params.setString(ordersBO.getShopId());
		}
		if (ordersBO.getStatus() != null && ordersBO.getStatus() != -1) {
			sql.append(" and o.status = ?");
			params.setInt(ordersBO.getStatus());
		}
		if (ordersBO.getState() != null && ordersBO.getState() == Is.YES.toIntValue()) {
			sql.append(" and o.status in (?,?,?)");
			params.setInt(OrderStatus.PAID.toIntValue());
			params.setInt(OrderStatus.SHIPPED.toIntValue());
			params.setInt(OrderStatus.WAITUSE.toIntValue());
		}
		if (StringUtils.isNotBlank(ordersBO.getShopOrderNumber())) {
			sql.append(" and o.shop_order_number like ?");
			params.setString("%" + ordersBO.getShopOrderNumber() + "%");
		}
		if (ordersBO.getType() != null){
			sql.append(" and o.type = ?");
			params.setInt(ordersBO.getType());
		}
		
		if(ordersBO.getPayWay() != null){
			sql.append(" and o.pay_way = ?");
			params.setInt(ordersBO.getPayWay());
		}

		if (StringUtils.isNotBlank(ordersBO.getMobilePhone())) {
			sql.append(" and a.phone like ?");
			params.setString("%" + ordersBO.getMobilePhone() + "%");
		}
		if (StringUtils.isNotBlank(ordersBO.getAddressName())) {
			sql.append(" and a.name like ?");
			params.setString("%" + ordersBO.getAddressName() + "%");
		}
		if (StringUtils.isNoneBlank(ordersBO.getName())) {
			sql.append(" and u.nick_name like ?");
			params.setString("%" + ordersBO.getName() + "%");
		}
		if (StringUtils.isNoneBlank(ordersBO.getUserMobilePhone())) {
			sql.append(" and u.mobile_phone like ?");
			params.setString("%" + ordersBO.getUserMobilePhone() + "%");
		}
		if (ordersBO.getStartDate() != null) {
			sql.append(" and o.pay_time >= ?");
			params.setDate(DateUtil.getDateStart(DateUtil.parseDate(ordersBO.getStartDate())));
		}
		if (ordersBO.getEndDate() != null) {
			sql.append(" and o.pay_time < ?");
			params.setDate(DateUtil.getDateEnd(DateUtil.parseDate(ordersBO.getEndDate())));
		}
		if (ordersBO.getBuyStartDate() != null) {
			sql.append(" and o.created_time >= ?");
			params.setDate(DateUtil.getDateStart(DateUtil.parseDate(ordersBO.getBuyStartDate())));
		}
		if (ordersBO.getBuyEndDate() != null) {
			sql.append(" and o.created_time < ?");
			params.setDate(DateUtil.getDateEnd(DateUtil.parseDate(ordersBO.getBuyEndDate())));
		}
		if (ordersBO.getUseStartDate() != null) {
			sql.append(" and o.use_time >= ?");
			params.setDate(DateUtil.getDateStart(DateUtil.parseDate(ordersBO.getUseStartDate())));
		}
		if (ordersBO.getUseEndDate() != null) {
			sql.append(" and o.use_time < ?");
			params.setDate(DateUtil.getDateEnd(DateUtil.parseDate(ordersBO.getUseEndDate())));
		}
		sql.append(" order by o.created_time desc,o.status asc");
		return super.queryForInt(sql.toString(), params);
	}

	@Override
	public List<OrdersBO> findAll(Integer pageNumber, Integer maxPageRow, OrdersBO ordersBO, Boolean isRefund) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();
		sql.append("select o.*,s.name as shop_name,l.name as logistics_name,l.`code` as logistics_code,mcu.privilege_money");
		sql.append(
				",a.name as user_name,a.phone as mobile_phone,a.address,a.area,u.nick_name as name,u.mobile_phone as user_mobile_phone");
		sql.append(" from orders o");

		if (isRefund) {// 查看退款商品对应的订单
			sql.append(
					" inner join (select og.order_id from orders_goods og  where (og.`status` = ? or og.`status` = ?)");
			sql.append(" group by og.order_id) og on og.order_id = o.id");
			params.setInt(OrderGoodsStatus.REFUND.toIntValue());
			params.setInt(OrderGoodsStatus.REFUND_SUCCESS.toIntValue());
		}

		sql.append(" inner join shop s on s.id=o.shop_id");
		sql.append(" inner join user u on u.id=o.user_id");
		if(StringUtils.isNotBlank(ordersBO.getChainId())){
			sql.append(" inner join chain c on s.chain_id=c.id");
		}
		sql.append(" left join address a on a.id= o.address_id");
		sql.append(" left join logistics l on o.logistics_id = l.id");
		sql.append(" left join member_card_use mcu on o.id=mcu.order_id and mcu.privilege_type=?");
		params.setInt(Is.YES.toIntValue());
		sql.append(" where o.is_delete = ?");
		
		params.setInt(IsDelete.NO.toIntValue());
		if(StringUtils.isNotBlank(ordersBO.getChainId())){
			sql.append(" and s.chain_id = ?");
			params.setString(ordersBO.getChainId());
		}
		if (StringUtils.isNotBlank(ordersBO.getShopId())) {
			sql.append(" and o.shop_id = ?");
			params.setString(ordersBO.getShopId());
		}
		if (ordersBO.getStatus() != null && ordersBO.getStatus() != -1) {
			sql.append(" and o.status = ?");
			params.setInt(ordersBO.getStatus());
		}
		if (ordersBO.getState() != null && ordersBO.getState() == Is.YES.toIntValue()) {
			sql.append(" and o.status in (?,?,?)");
			params.setInt(OrderStatus.PAID.toIntValue());
			params.setInt(OrderStatus.SHIPPED.toIntValue());
			params.setInt(OrderStatus.WAITUSE.toIntValue());
		}
		if (StringUtils.isNotBlank(ordersBO.getShopOrderNumber())) {
			sql.append(" and o.shop_order_number like ?");
			params.setString("%" + ordersBO.getShopOrderNumber() + "%");
		}
		if (ordersBO.getType() != null){
			sql.append(" and o.type = ?");
			params.setInt(ordersBO.getType());
		}
		
		if(ordersBO.getPayWay() != null){
			sql.append(" and o.pay_way = ?");
			params.setInt(ordersBO.getPayWay());
		}

		if (StringUtils.isNotBlank(ordersBO.getMobilePhone())) {
			sql.append(" and a.phone like ?");
			params.setString("%" + ordersBO.getMobilePhone() + "%");
		}
		if (StringUtils.isNotBlank(ordersBO.getAddressName())) {
			sql.append(" and a.name like ?");
			params.setString("%" + ordersBO.getAddressName() + "%");
		}
		if (StringUtils.isNoneBlank(ordersBO.getName())) {
			sql.append(" and u.nick_name like ?");
			params.setString("%" + ordersBO.getName() + "%");
		}
		if (StringUtils.isNoneBlank(ordersBO.getUserMobilePhone())) {
			sql.append(" and u.mobile_phone like ?");
			params.setString("%" + ordersBO.getUserMobilePhone() + "%");
		}
		if (ordersBO.getStartDate() != null) {
			sql.append(" and o.pay_time >= ?");
			params.setDate(DateUtil.getDateStart(DateUtil.parseDate(ordersBO.getStartDate())));
		}
		if (ordersBO.getEndDate() != null) {
			sql.append(" and o.pay_time < ?");
			params.setDate(DateUtil.getDateEnd(DateUtil.parseDate(ordersBO.getEndDate())));
		}
		if (ordersBO.getBuyStartDate() != null) {
			sql.append(" and o.created_time >= ?");
			params.setDate(DateUtil.getDateStart(DateUtil.parseDate(ordersBO.getBuyStartDate())));
		}
		if (ordersBO.getBuyEndDate() != null) {
			sql.append(" and o.created_time < ?");
			params.setDate(DateUtil.getDateEnd(DateUtil.parseDate(ordersBO.getBuyEndDate())));
		}
		if (ordersBO.getUseStartDate() != null) {
			sql.append(" and o.use_time >= ?");
			params.setDate(DateUtil.getDateStart(DateUtil.parseDate(ordersBO.getUseStartDate())));
		}
		if (ordersBO.getUseEndDate() != null) {
			sql.append(" and o.use_time < ?");
			params.setDate(DateUtil.getDateEnd(DateUtil.parseDate(ordersBO.getUseEndDate())));
		}
		sql.append(" order by o.created_time desc,o.status asc");

		if (pageNumber != null && maxPageRow != null) {
			return super.queryForPagingList(sql.toString(), OrdersBO.class, params, pageNumber, maxPageRow);
		} else {
			return super.queryForList(sql.toString(), OrdersBO.class, params);
		}
	}
	

	public OrdersBO findOrderId(String orderId) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();
		sql.append("select o.*,a.name as user_name,a.phone as mobile_phone,a.area,a.address");
		sql.append(",s.name as shop_name,s.address as shop_address,s.shop_head_img_url");
		sql.append(",u.id as user_id,l.`code` as logistics_code,l.name as logistics_name");
		sql.append(",u.nick_name as name,u.open_id,u.mobile_phone as user_mobile_phone,s.lat,s.lng,s.mobile_phone as shopMobilePhone ");
		sql.append(",(select name from area where is_delete=? and id=s.area_province_id) 'province'");
		sql.append(",(select name from area where is_delete=? and id=s.area_city_id) 'city'");
		sql.append(",(select name from area where is_delete=? and id=s.area_county_id) 'county' ");
		sql.append(" from orders o");
		sql.append(" inner join shop s on s.id=o.shop_id");
		sql.append(" inner join user u on u.id=o.user_id");
		sql.append(" left join address a on a.id= o.address_id");
		sql.append(" left join logistics l on o.logistics_id = l.id");
		sql.append(" where o.is_delete = ? and o.id=? ");
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		
		params.setInt(IsDelete.NO.toIntValue());
		params.setString(orderId);
		return super.query(sql.toString(), OrdersBO.class, params);
	}
	
	public OrdersPrintingBO findOrderIdToPrinting(String orderId){
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();
		sql.append("select distinct o.id,o.table_number,o.food_box_charge,o.real_money,o.type,o.coupons_money,o.pay_money,o.shop_order_number,o.pay_way,o.pay_time ");
		sql.append(",o.remark,o.consume_name,o.consume_phone,o.order_number,o.postage ");
		sql.append(",a.name as user_name,a.phone as mobile_phone,a.area,a.address");
		sql.append(",s.name as shop_name ");
		sql.append(" from orders o");
		sql.append(" left join shop s on s.id=o.shop_id ");
		sql.append(" left join address a on a.id= o.address_id ");
		sql.append(" where o.is_delete = ? and o.id=? ");
		params.setInt(IsDelete.NO.toIntValue());
		params.setString(orderId);
		return super.query(sql.toString(), OrdersPrintingBO.class, params);
	}

	public List<OrdersBO> orders(Integer pageNumber, Integer maxPageRow, Integer status,Integer type, String userId,String shopId) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();
		sql.append(
				" select s.id as shop_id,s.name as shop_name,o.id,o.food_box_charge,o.created_time,o.`status`,o.postage,o.pay_way,o.pay_money");
		sql.append(
				" ,o.price_difference,o.order_number,s.shop_head_img_url,o.shop_order_number,o.logistics_number");
		sql.append(" ,o.`type`");
		sql.append(" from orders o");
		sql.append(" inner join shop s on s.id = o.shop_id");
		sql.append(" where o.user_id = ? and o.is_delete = ?");
		params.setString(userId);
		params.setInt(IsDelete.NO.toIntValue());
		if (status != null) {
			if (status == OrderStatus.CONFIRM.toIntValue()) {
				sql.append(" and (o.status =? or o.status = ?)");
				params.setInt(status);
				params.setInt(OrderStatus.HASUSE.toIntValue());
			} else {
				sql.append(" and o.status =?");
				params.setInt(status);
			}
		}
		if(type != null){
			sql.append(" and o.type =?");
			params.setInt(type);
		}
		
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" and o.shop_id = ?");
			params.setString(shopId);
		}
		
		sql.append(" order by o.created_time desc");
		return super.queryForPagingList(sql.toString(), OrdersBO.class, params, pageNumber, maxPageRow);
	}
	
	public int getGoodsTotalCount(Integer status, String userId,Integer type) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();
		sql.append("select count(o.id) from orders o");
		sql.append(" inner join shop s on s.id = o.shop_id");
		sql.append(" where o.user_id = ? and o.is_delete = ?");
		params.setString(userId);
		params.setInt(IsDelete.NO.toIntValue());
		if (status != null) {
			if (status == OrderStatus.CONFIRM.toIntValue()) {// 3
				sql.append(" and ( o.status =? or o.status = ?)");// = 3 or = 5
				params.setInt(status);
				params.setInt(OrderStatus.HASUSE.toIntValue());
			} else {
				sql.append(" and o.status =?"); // 3
				params.setInt(status);
			}
		}
		
		if(type != null){
			sql.append(" and o.type =?");
			params.setInt(type);
		}
		
		sql.append(" order by o.created_time desc");
		return super.queryForInt(sql.toString(), params);
	}

	@Override
	public OrdersBO findOrderOffline(String orderNumber) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append("select o.id,o.type,o.table_number,o.food_box_charge,o.pay_way payWay,o.deduction_money deductionMoney,o.order_number,o.status,s.name 'shop_name',s.address 'shop_address',s.lat,s.lng,");
		sql.append("s.mobile_phone,o.pay_money,o.coupons_money,o.pay_time,o.shop_id,og.goods_id,og.file_name 'cover_image',o.use_time,");
		sql.append("o.validation_code,o.qr_code,o.active_date,og.goods_name,og.price,og.hint,o.shop_order_number,og.goods_code,og.number,");
		sql.append("(select name from area where is_delete=? and id=s.area_province_id) 'province',");
		sql.append("(select name from area where is_delete=? and id=s.area_city_id) 'city',");
		sql.append("(select name from area where is_delete=? and id=s.area_county_id) 'county'");
		sql.append(" from orders o");
		sql.append(" inner join shop s on s.id=o.shop_id");
		sql.append(" inner join orders_goods og on o.id=og.order_id");
		sql.append(" where o.is_delete = ? and o.order_number= ?");

		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		params.setString(orderNumber);
		return super.query(sql.toString(), OrdersBO.class, params);
	}

	@Override
	public List<Orders> getOrdersDefaultEvaluate(Date date) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();

		sql.append("select id");
		sql.append(" from orders");
		sql.append(" where is_delete=? and status = ? and updated_time < ?");

		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(OrderStatus.CONFIRM.toIntValue());
		params.setDate(date);

		return super.queryForList(sql.toString(), Orders.class, params);
	}

	@Override
	public List<Orders> getShippedOrders() {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();

		sql.append("select * from orders where is_delete=? and status=?");

		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(OrderStatus.SHIPPED.toIntValue());

		return super.queryForList(sql.toString(), Orders.class, params);
	}

	@Override
	public List<Orders> getOrderByOrderNumber(String orderNumber) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from orders where order_number =? and is_delete =? ");
		StatementParameter params = new StatementParameter();
		params.setString(orderNumber);
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForList(sql.toString(), Orders.class, params);
	}

	@Override
	public int orderTotalCount(String userId) {
		String sql = "select count(*) from orders where user_id = ? and is_delete =?";
		StatementParameter params = new StatementParameter();
		params.setString(userId);
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForInt(sql, params);
	}

	@Override
	public OrdersBO getcheckOrders(Orders orders) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select");
		sql.append(
				" o.id,o.order_number,o.shop_order_number,o.food_box_charge,o.use_time,o.pay_way,o.created_time,o.pay_money,o.active_date,o.validation_code,o.`status`,");
		sql.append(" og.goods_name,og.price,og.file_name coverImage,og.goods_id,o.shop_id,o.user_id,og.id as orderGoodsId");
		sql.append(" from orders o");
		sql.append(" inner join orders_goods og on og.order_id=o.id");
		sql.append(" where (o.`status`=? or o.`status`=?)");

		StatementParameter param = new StatementParameter();
		param.setInt(OrderStatus.WAITUSE.toIntValue());
		param.setInt(OrderStatus.HASUSE.toIntValue());

		if (StringUtils.isNotBlank(orders.getValidationCode())) {
			sql.append(" and o.validation_code=?");
			param.setString(orders.getValidationCode());
		}
		if (StringUtils.isNotBlank(orders.getShopId())) {
			sql.append(" and o.shop_id=?");
			param.setString(orders.getShopId());
		}

		return query(sql.toString(), OrdersBO.class, param);
	}

	public List<Orders> getOrderByPay(String userId, String orderNumber) {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from orders where user_id = ? and order_number = ?");
		StatementParameter params = new StatementParameter();
		params.setString(userId);
		params.setString(orderNumber);
		return super.queryForList(sql.toString(), Orders.class, params);
	}

	public Orders findByShopOrderNumber(String userId, String shopOrderNumber) {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from orders where user_id = ? and shop_order_number = ?");
		StatementParameter params = new StatementParameter();
		params.setString(userId);
		params.setString(shopOrderNumber);
		return super.query(sql.toString(), Orders.class, params);
	}

	@Override
	public List<UserEvaluate> getDefaultEvaluate(Date date) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();

		sql.append("select o.shop_id,o.user_id,og.goods_id,og.id orders_goods_id");
		sql.append(" from orders o");
		sql.append(" inner join orders_goods og on o.id=og.order_id");
		sql.append(" where o.is_delete=? and og.is_delete =?");
		sql.append(" and o.status=? and o.updated_time < ?");

		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(OrderStatus.CONFIRM.toIntValue());
		params.setDate(date);

		return super.queryForList(sql.toString(), UserEvaluate.class, params);
	}

	public List<Orders> findByStatus(Integer status) {
		String sql = "select id,`status`,created_time from orders where `status` = ?";
		StatementParameter params = new StatementParameter();
		params.setInt(status);
		return queryForList(sql, Orders.class, params);
	}

	@Override
	public Orders getOrderByShopOrderNumber(String shopOrderNumber) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select id,shop_order_number,pay_time,pay_money,`status`,shop_id from orders where shop_order_number =? and is_delete =? ");
		StatementParameter params = new StatementParameter();
		params.setString(shopOrderNumber);
		params.setInt(IsDelete.NO.toIntValue());
		return super.query(sql.toString(), Orders.class, params);
	}

	public OrdersBO findLogistics(String orderId) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();
		sql.append("select o.id,o.logistics_number,o.logistics_id,l.`code` as logistics_code,l.name as logistics_name");
		sql.append(" from orders o");
		sql.append(" left join logistics l on o.logistics_id = l.id");
		sql.append(" where o.is_delete = ?  ");
		params.setInt(IsDelete.NO.toIntValue());
		if (StringUtils.isNoneBlank(orderId)) {
			sql.append(" and o.id = ? ");
			params.setString(orderId);
		}
		return super.query(sql.toString(), OrdersBO.class, params);
	}

	@Override
	public Orders getOrdersByOrderNumber(String orderNumber) {
		String sql = "select * from orders where order_number=?";
		StatementParameter param = new StatementParameter();
		param.setString(orderNumber);
		return query(sql, Orders.class, param);
	}

	@Override
	public Integer getPaidOrderCount(String shopId) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();

		sql.append("select count(*) from orders");
		sql.append(" where is_delete=? and shop_id=? and status in (?,?)");

		params.setInt(IsDelete.NO.toIntValue());
		params.setString(shopId);
		params.setInt(OrderStatus.PAID.toIntValue());
		params.setInt(OrderStatus.WAITUSE.toIntValue());

		return queryForInt(sql.toString(), params);
	}

	@Override
	public List<OrdersBO> getOrderByConfirmTime(Date date,Integer isOpenSell) {
		StringBuffer sql = new StringBuffer();
		sql.append("select o.id,o.pay_time,o.`status`,g.price as goodsPrice,o.pay_money,g.shop_id,o.user_id,g.`name`");
		sql.append(",og.goods_id,o.type,o.food_box_charge,og.number as goodsNumber,og.id as orderGoodsId ,g.is_open_rebate,g.profit,g.is_open_sell");
		sql.append(",o.id,og.price as price,o.order_number,og.pay_money as orderGoodsPayMoney,o.shop_order_number");
		sql.append(" from orders_goods og");
		sql.append(" inner join orders o on o.id = og.order_id");
		sql.append(" inner join goods g on g.id = og.goods_id ");
		sql.append(" where o.is_delete = ? and o.`status` not in (?,?,?,?) and g.is_open_rebate = ? and g.is_open_sell =?");
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(OrderStatus.WAIT.toIntValue());
		params.setInt(OrderStatus.CANCEL.toIntValue());
		params.setInt(OrderStatus.FAILIN.toIntValue());
		params.setInt(OrderStatus.FAIL.toIntValue());
		params.setInt(Is.NO.toIntValue());
		params.setInt(isOpenSell);
		if (date != null) {
			sql.append(" and confirm_time >= ? and confirm_time < ?");
			params.setDate(DateUtil.getDateStart(date));
			params.setDate(DateUtil.getDateEnd(date));
		}
		return super.queryForList(sql.toString(), OrdersBO.class, params);
	}

	@Override
	public Double sumPayMoney(String userId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select ifnull(sum(pay_money),0) from orders where is_delete = ? ");
		sql.append(" and user_id = ? and `status` not in (?,?,?,?)");
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		params.setString(userId);
		params.setInt(OrderStatus.WAIT.toIntValue());
		params.setInt(OrderStatus.CANCEL.toIntValue());
		params.setInt(OrderStatus.FAILIN.toIntValue());
		params.setInt(OrderStatus.FAIL.toIntValue());
		return super.queryForDouble(sql.toString(), params);
	}

	public List<OrdersBO> findOrderByid(String orderGoodsId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select o.pay_time,g.is_open_rebate,o.`status`,o.food_box_charge,g.price as goodsPrice,o.pay_money,g.shop_id,o.user_id,g.`name`");
		sql.append(",o.order_number,og.goods_id,o.type,og.number as goodsNumber,og.id as orderGoodsId ,g.is_open_rebate");
		sql.append(",g.profit,o.id,og.price,og.pay_money as orderGoodsPayMoney");
		sql.append(" from orders_goods og ");
		sql.append(" inner join orders o on o.id = og.order_id");
		sql.append(" inner join goods g on g.id = og.goods_id ");
		sql.append(" where o.is_delete = ? and og.id = ?");
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		params.setString(orderGoodsId);
		return super.queryForList(sql.toString(), OrdersBO.class, params);
	}

	@Override
	public List<Orders> getOrderByStatus() {
		String sql = "select * from orders where `status` not in(?,?) order by pay_time desc";
		StatementParameter params = new StatementParameter();
		params.setInt(OrderStatus.WAIT.toIntValue());
		params.setInt(OrderStatus.CANCEL.toIntValue());
		return super.queryForList(sql, Orders.class, params);
	}

	@Override
	public int getTotal(String chainId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(o.id) from orders o inner join shop s on o.shop_id=s.id");
		sql.append(" where s.chain_id = ? and o.is_delete = ?");
		StatementParameter params = new StatementParameter();
		params.setString(chainId);
		params.setInt(IsDelete.NO.toIntValue());
		return queryForInt(sql.toString(), params);

	}

	@Override
	public OrdersBO findOrderById(String id) {
		StringBuffer sql = new StringBuffer();
		sql.append("select g.profit,g.is_open_sell,g.is_open_rebate,g.price,o.*");
		sql.append(" from orders o");
		sql.append(" inner join orders_goods og on o.id = og.order_id");
		sql.append(" inner join goods g on g.id = og.goods_id where o.id = ?");
		StatementParameter params = new StatementParameter();
		params.setString(id);
		return super.query(sql.toString(), OrdersBO.class, params);
	}

	@Override
	public List<OrdersAndDetailsBO> findOdersAndDetails(OrdersAndDetailsBO oadb) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append("select s.name shop_name,g.is_open_rebate,l.name logistics_name,"
				+ "o.shop_order_number shop_order_number,o.remark,o.food_box_charge,g.name goods_name,t.name type_name,"
				+ "o.logistics_number,o.real_money,o.coupons_money,o.postage,o.pay_money,o.id,"
				+ "o.price_difference,o.pay_way,o.created_time,o.pay_time,o.delivery_time,o.use_time,"
				+ "o.confirm_time,o.complete_time,o.status,o.order_number,o.transaction_number,o.type,"
				+ "u.name userName,u.mobile_phone user_mobile_phone,"
				+ "a.area address_area,a.address address_address,a.name address_name,a.phone address_phone,"
				+ "og.sku_content_name orders_goods_standard,og.number orders_goods_number,"
				+ "og.price orders_goods_price,og.status orders_goods_status,og.id orders_goods_id");
		sql.append(" from orders o");
		sql.append(" inner join shop s on o.shop_id = s.id");
		sql.append(" inner join user u on o.user_id = u.id");
		sql.append(" left join address a on o.address_id = a.id");
		sql.append(" left join logistics l on o.logistics_id = l.id");
		sql.append(" inner join orders_goods og on o.id = og.order_id");
		sql.append(" inner join goods g on og.goods_id = g.id");
		sql.append(" left join type t on t.id = og.type_id");
		if(oadb.getChainId()!=null){
			sql.append(" inner join chain c on s.chain_id=c.id");	
		}
		sql.append(" where o.is_delete = ?");
		sql.append(" and o.created_time between ? and ?");

		params.setInt(IsDelete.NO.toIntValue());
		params.setDate(oadb.getStartTime());
		params.setDate(oadb.getEndTime());
		
		if(oadb.getShopId()!=null){
			sql.append(" and s.id = ?");
			params.setString(oadb.getShopId());
		}
		
		if(oadb.getChainId()!=null){
			sql.append(" and c.id=?");
			params.setString(oadb.getChainId());
		}

		if (oadb.getOrdersStatus() != null) {
			sql.append(" and o.status = ?");
			params.setInt(oadb.getOrdersStatus());
		}

		if (oadb.getType() != null) {
			sql.append(" and o.type = ?");
			params.setInt(oadb.getType());
		}
		sql.append(" order by o.created_time desc");

		return super.queryForList(sql.toString(), OrdersAndDetailsBO.class, params);
	}

	@Override
	public List<Orders> findWaitConfirm(Date date,Integer status) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append("select * from orders where status = ? and type = ? and is_delete = ?");
		params.setInt(status);
		params.setInt(OrderType.DELIVERY.toIntValue());
		params.setInt(Is.NO.toIntValue());
		if(status == OrderStatus.COMPLETE.toIntValue()){
			if(date != null){
				sql.append(" and complete_time >= ? and complete_time < ?");
				params.setDate(DateUtil.getDateStart(date));
				params.setDate(DateUtil.getDateEnd(date));
			}
		}else if(status == OrderStatus.SHIPPED.toIntValue()){
			if (date != null) {
				sql.append(" and delivery_time >= ? and delivery_time < ?");
				params.setDate(DateUtil.getDateStart(date));
				params.setDate(DateUtil.getDateEnd(date));
			}
		}
		
		return super.queryForList(sql.toString(), Orders.class, params);
	}

	@Override
	public OrdersTransactionInfoBO countUserTransaction(String userId, String csId,Integer isChain,Date receiveDate) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) as countTransaction,sum(pay_money) as sumPayMoney from orders o");
		sql.append(" left join shop s on o.shop_id = s.id where o.user_id = ?  and o.status = ? ");
		StatementParameter param = new StatementParameter();
		param.setString(userId);
		
		param.setInt(OrderStatus.COMPLETE.toIntValue());
		if(isChain == IsChainStatus.NO.toIntStatus()){
			sql.append(" and o.shop_id = ? ");
			param.setString(csId);
		}else{
			sql.append(" and s.chain_id = ? ");
			param.setString(csId);
		}
		if(receiveDate!=null){
			sql.append(" and o.created_time >= ?  ");
			param.setDate(receiveDate);
		}
		sql.append(" and o.is_delete = ?  ");
		param.setInt(IsDelete.NO.toIntValue());
		
		return super.query(sql.toString(), OrdersTransactionInfoBO.class, param);
	}

	@Override
	public List<CountOrdersStatusBO> countOrdersStatus(String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select `status` as `key`,count(*) as `value`  from orders ");
		sql.append(" where shop_id = ? and is_delete = ? ");
		sql.append(" group by `status` order by `status` ");
		StatementParameter param = new StatementParameter();
		param.setString(shopId);
		param.setInt(IsDelete.NO.toIntValue());
		return super.queryForList(sql.toString(),CountOrdersStatusBO.class, param);
	}

	@Override
	public OrdersBO findReserveInfo(String ordersId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select o.reserve_time,o.consume_phone,o.consume_name,st.name 'technician_name',g.name 'serve_name'");
		sql.append(" from orders o");
		sql.append(" left join shop_technician st on o.technician_id = st.id");
		sql.append(" left join goods g on g.id = o.serve_id");
		sql.append(" where o.id = ? and o.is_delete = ?");
		StatementParameter param = new StatementParameter();
		param.setString(ordersId);
		param.setInt(Is.NO.toIntValue());
		return super.query(sql.toString(), OrdersBO.class, param);
	}
}
